package myJDBC.lowLevel;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import myJDBC.myConnection;

public class Params {
	public static void createParamVarchNumb(int attrId, int objId, String val) {

		try (PreparedStatement pstmt = myConnection.dbConnection
				.prepareStatement("insert into \"params\"(\"attribute_id\", \"object_id\", \"valueVarch\") values (?,?,?)"); ) {

			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.setString(3, val);
			pstmt.executeUpdate();
		} catch (SQLException e) {
	          System.out.println(e.getMessage());
	    }
	}
	
	 public static void createParamDate(int attrId, int objId, String val) {

        try (PreparedStatement pstmt = myConnection.dbConnection
                .prepareStatement("insert into \"params\"(\"attribute_id\", \"object_id\", \"valueDate\") values (?,?,TO_DATE(?, 'DD-MM-YYYY'))")) {

             pstmt.setInt(1, attrId);
             pstmt.setInt(2, objId);
             pstmt.setString(3, val);
             pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
     }  


	public static void createParamDate(int attrId, int objId, String val, String format) {
		
		try (PreparedStatement pstmt = myConnection.dbConnection
                .prepareStatement("insert into \"params\"(\"attribute_id\", \"object_id\", \"valueDate\") values (?,?,TO_DATE(?,?))")) {

            pstmt.setInt(1, attrId);
            pstmt.setInt(2, objId);
            pstmt.setString(3, val);
            pstmt.setString(4, format);
            pstmt.executeUpdate();
		} catch (SQLException e) {
            System.out.println(e.getMessage());
        }
	}

	public static void createParamArray(int attrId, int objId, int val) {
		try (PreparedStatement pstmt = myConnection.dbConnection
                .prepareStatement("insert into \"params\"(\"attribute_id\", \"object_id\", \"list_value_id\") values (?,?,?)")) {
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.setInt(3, val);
			pstmt.executeUpdate();
		} catch (SQLException e) {
            System.out.println(e.getMessage());
        }
	}

	public static String returnVarchNumb(int attrId, int objId) {
		String tmp = "";
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"valueVarch\" ")
				           .append("from \"params\" ")
				           .append("where \"attribute_id\"=? and \"object_id\"=?");
			try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				tmp = rs.getString(1);
			}
		} catch (SQLException e) {
            System.out.println(e.getMessage());
        }
		return tmp;
	}

	public static String returnDate(int attrId, int objId) {
		String tmp = "";
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"valueDate\" ")
		                   .append("from \"params\" ")
				           .append("where \"attribute_id\"=? and \"object_id\"=?");
			
		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				tmp = rs.getString(1);
			}
		}  catch (SQLException e) {
            System.out.println(e.getMessage());
        }
		return tmp;
	}

	public static String returnArrValue(int attrId, int objId) {
		String tmp = "";
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"value\" ")
				           .append("from \"list_values\" ").append("where \"list_value_id\" in(")
				           .append("select \"list_value_id\" ").append( "from \"params\" ")
				           .append("where \"attribute_id\"=? and \"object_id\"=?)");

		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				tmp = rs.getString(1);
			}
		}  catch (SQLException e) {
            System.out.println(e.getMessage());
        }
		return tmp;
	}

	public static String returnArrValueId(int attrId, int objId) {
		String tmp = "";
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"list_value_id\" ").append( "from \"params\" ")
				           .append("where \"attribute_id\"=? and \"object_id\"=?");

		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, attrId);
			pstmt.setInt(2, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				tmp = rs.getString(1);
			}
		}  catch (SQLException e) {
            System.out.println(e.getMessage());
        }
		return tmp;
	}
	
	public static void changeValueId(int objId, int attrId, int listValId) {
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("UPDATE \"params\" ")
						   .append("SET \"list_value_id\"=? ")
						   .append("where \"object_id\"=? and \"attribute_id\"=? ");
		if (myConnection.dbConnection != null) {
			   try (PreparedStatement pstmt = myConnection.dbConnection
		                .prepareStatement(selectAttributes.toString())) {
					pstmt.setInt(1, listValId);
					pstmt.setInt(2, objId);
					pstmt.setInt(3, attrId);
					pstmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
	}
	
	//{varch{atrid, val}  list{atrid, val}   date{atrid, val}   val{atrid, val}}
	public static ArrayList<ArrayList<String>> retParams(int objId) {
		ArrayList<ArrayList<String>> ret= new ArrayList<ArrayList<String>>();
		ArrayList<String> varch=new ArrayList<String>();
		ArrayList<String> list=new ArrayList<String>();
		ArrayList<String> date=new ArrayList<String>();
		ArrayList<String> val=new ArrayList<String>();
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"attribute_id\", \"list_value_id\", \"valueVarch\",\"valueDate\", \"value\" ")
						   .append("from \"params\" ")
						   .append("where \"object_id\"=?");
		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				if(rs.getString(2)!=null){//list
					list.add(rs.getString(1));//id
					list.add(rs.getString(2));
				}
				if(rs.getString(3)!=null){//varchar
					varch.add(rs.getString(1));//id
					varch.add(rs.getString(3));
				}
				if(rs.getString(4)!=null){//date
					date.add(rs.getString(1));//id
					date.add(rs.getString(4));
				}
				if(rs.getString(5)!=null){//value
					val.add(rs.getString(1));//id
					val.add(rs.getString(5));
				}
			}
			ret.add(varch);
			ret.add(list);
			ret.add(date);
			ret.add(val);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return ret;
	}
	
	public static void UpdateParamVarchNumb(int attrId, int objId, String val) {
		StringBuilder selectObjects = new StringBuilder();
		  selectObjects.append("update \"params\" ")
				       .append("set \"valueVarch\"= ? ")
				       .append("where \"object_id\"=? ")
				       .append("and \"attribute_id\"=? ");
				
        if (myConnection.dbConnection != null) {
 		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectObjects.toString())) {
 			   pstmt.setString(1, val);
			   pstmt.setInt(2, objId);
			   pstmt.setInt(3, attrId);
			   pstmt.executeUpdate();          
        } catch (SQLException e) {
          System.out.println(e.getMessage());
        }
       }		
	}
	public static void UpdateParamDate(int attrId, int objId, String val, String format) {
		StringBuilder selectObjects = new StringBuilder();
		  selectObjects.append("update \"params\" ")
				       .append("set \"valueDate\"= TO_DATE(?,?)")
				       .append("where \"object_id\"=? ")
				       .append("and \"attribute_id\"=? ");
				
        if (myConnection.dbConnection != null) {
  		   try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectObjects.toString())) {
			   pstmt.setString(1, val);
			   pstmt.setString(2, format);
			   pstmt.setInt(3, objId);
			   pstmt.setInt(4, attrId);
			   pstmt.executeUpdate();          
        } catch (SQLException e) {
          System.out.println(e.getMessage());
        }
       }		
	}	
} 
